﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using MongoDB;
using gudusoft.gsqlparser;
using SkynetMongoDBAccess.Converter;

namespace SkynetMongoDBAccess
{
    internal class ConvertSql
    {

        public object ConvertSelect(string text, MongoDatabase db)
        {
            text = text.Replace(Environment.NewLine, " ").Replace("\n", " ").Replace("\r", " ");
            int selectIndex = text.IndexOf("select", StringComparison.CurrentCultureIgnoreCase);
            int fromIndex = text.IndexOf("from", StringComparison.CurrentCultureIgnoreCase);
            int whereIndex = text.IndexOf("where", StringComparison.CurrentCultureIgnoreCase);
            int orderIndex = text.IndexOf("order by", StringComparison.CurrentCultureIgnoreCase);

            if (selectIndex == -1 || fromIndex == -1)
                throw new Exception("命令错误");
            selectIndex += 6;
            fromIndex += 4;
           
           
            string tableName = string.Empty;
            if (whereIndex == -1)
            {
                tableName = text.Substring(fromIndex).Trim();
            }
            else
            {
                whereIndex += 5;
                tableName = text.Substring(fromIndex, whereIndex - fromIndex).Trim();
            }
            string fieldName = text.Substring(selectIndex, fromIndex - selectIndex).Trim();

            //TGSqlParser parser = new TGSqlParser(TDbVendor.DbVMssql);
            //parser.SqlText.Text = text;
            //parser.Parse();
            //if (parser.ErrorCount > 0)
            //{
            //    throw new Exception(parser.ErrorMessages);
            //}
            //TSelectSqlStatement sql = parser.SqlStatements[0] as TSelectSqlStatement;

            IQueryable<Document> query = db.GetCollection(tableName).Linq();

            string[] fields = fieldName.Split(new char[] {','}, StringSplitOptions.RemoveEmptyEntries);
            for (int i = 0; i < fields.Length;i++ )
            {
                if (fields[i].ToLower().Contains("count"))
                {
                    return query.Count();
                }
                if (fields[i].Contains("*"))
                {
                    return query.ToList().ConvertToDataSet();
                }
                if (fields[i].ToLower().StartsWith("top"))
                {
                    int topcount = Convert.ToInt32(fields[i].Split(new string[] {" "}, StringSplitOptions.RemoveEmptyEntries)[1]);
                    query = query.Take(topcount);
                }
            }
            if (orderIndex > 0)
            {
                orderIndex += 8;
                string orderby = text.Substring(orderIndex).Trim();
                string[] sortClause = orderby.Split(new char[] {','}, StringSplitOptions.RemoveEmptyEntries);
                for (int i = 0; i < sortClause.Count(); i++)
                {
                    string s = sortClause[i];
                    if (s.ToLower().IndexOf(" desc", StringComparison.InvariantCultureIgnoreCase) > 0)
                    {
                        s = Regex.Replace(s, " desc", "", RegexOptions.IgnoreCase);

                        query = query.OrderByDescending(o => o[s]);
                    }
                    else
                    {
                        s = Regex.Replace(s, " asc", "", RegexOptions.IgnoreCase);
                        query = query.OrderBy(o => o[s]);
                    }
                }
            }



            return query.ToList().ConvertToDataSet();
           

           

            //ChangeSqlToJs(sql.WhereClause);


            //if (!iscount)
            //{
            //    if (!string.IsNullOrEmpty(sql.WhereClauseAsPrettyText))
            //    {
            //        string code = sql.WhereClauseAsPrettyText.Replace("\r\n", "").HandleLike();
            //        query = query.Spec(new Document().Append("$where", new Code(code)));
            //    }
            //    if (fields.Count > 0)
            //        query = query.Fields(fields);
            //    if (limit != -1)
            //        query = query.Limit(limit);
            //    if (orders.Count > 0)
            //        query = query.Sort(orders);
            //    var docs = query.Documents.ToList();


            //    return docs.ConvertToDataSet();
            //}
            //else
            //{
            //    Document doc = new Document();
            //    var table = db.GetCollection(sql.Tables[0].AsText);
            //    long c = 0;
            //    if (!string.IsNullOrEmpty(sql.WhereClauseAsPrettyText))
            //    {
            //        string code = sql.WhereClauseAsPrettyText.Replace("\r\n", "").HandleLike();
            //        c = table.Count(new Document().Append("$where", new Code(code)));
            //    }
            //    else
            //        c = table.Count();
            //    doc.Add("Count()", c);
            //    return new List<Document> { doc }.ConvertToDataSet();
            //}

        }

        public void ConvertUpdate(string text, MongoDatabase db)
        {
            TGSqlParser parser = new TGSqlParser(TDbVendor.DbVMssql);
            parser.SqlText.Text = text;
            parser.Parse();
            if (parser.ErrorCount > 0)
            {
                throw new Exception(parser.ErrorMessages);
            }

            TUpdateSqlStatement sql = parser.SqlStatements[0] as TUpdateSqlStatement;

            ChangeSqlToJs(sql.WhereClause);

            var table = db.GetCollection(sql.Tables[0].AsText);

            Document doc = new Document();
            for (int i = 0; i < sql.Fields.Count(); i++)
            {
                string key = sql.Fields[i].AsText.Split('=')[0].Trim();
                string value = sql.Fields[i].AsText.Split('=')[1].Trim();
                if (value.StartsWith("'"))
                {
                    DateTime dt;
                    if (DateTime.TryParse(value.Replace("'", ""), out dt))
                    {
                        doc.Add(key, dt);
                    }
                    else
                    {
                        doc.Add(key, value.Replace("'", ""));
                    }
                }
                else
                {
                    Int32 ii = 0;
                    if (Int32.TryParse(value, out ii))
                        doc.Add(key, ii);
                    else
                        doc.Add(key, value);
                }


                Document where = new Document();
                if (!string.IsNullOrEmpty(sql.WhereClauseAsPrettyText))
                    where.Add("$where", new Code(sql.WhereClauseAsPrettyText.Replace("\r\n", "")));
                table.UpdateAll(doc, where);
            }
        }

        public void ConvertDelete(string text, MongoDatabase db)
        {
            TGSqlParser parser = new TGSqlParser(TDbVendor.DbVMssql);
            parser.SqlText.Text = text;
            parser.Parse();
            if (parser.ErrorCount > 0)
            {
                throw new Exception(parser.ErrorMessages);
            }

            TDeleteSqlStatement sql = parser.SqlStatements[0] as TDeleteSqlStatement;
            ChangeSqlToJs(sql.WhereClause);

            var table = db.GetCollection(sql.Tables[0].AsText);
            Document doc = new Document();
            if (!string.IsNullOrEmpty(sql.WhereClauseAsPrettyText))
                doc.Add("$where", new Code(sql.WhereClauseAsPrettyText.Replace("\r\n", "")));
            table.Delete(doc);

        }

        public void ConvertInsert(string text, MongoDatabase db)
        {
            TGSqlParser parser = new TGSqlParser(TDbVendor.DbVMssql);
            parser.SqlText.Text = text;
            parser.Parse();
            if (parser.ErrorCount > 0)
            {
                throw new Exception(parser.ErrorMessages);
            }

            TInsertSqlStatement sql = parser.SqlStatements[0] as TInsertSqlStatement;


            var table = db.GetCollection(sql.Tables[0].AsText);
            Document doc = new Document();
            for (int i = 0; i < sql.Fields.Count(); i++)
            {
                string key = sql.Fields[i].AsText;
                string value = (sql.MultiValues[0] as TLzFieldList)[i].AsText;
                if (value.StartsWith("'"))
                {
                    DateTime dt;
                    if (DateTime.TryParse(value.Replace("'", ""), out dt))
                    {
                        doc.Add(key, dt);
                    }
                    else
                    {
                        doc.Add(key, value.Replace("'", ""));
                    }
                }
                else
                {
                    Int32 ii = 0;
                    if (Int32.TryParse(value, out ii))
                        doc.Add(key, ii);
                    else
                        doc.Add(key, value);

                }
                table.Insert(doc);
            }
        }




        private void ChangeSqlToJs(TLzCustomExpression pExpr)
        {
            if (pExpr != null)
            {
                pExpr.PreOrderTraverse(ChangeSqlToJsVisitor);
            }
        }


        private bool ChangeSqlToJsVisitor(TLz_Node pnode, Boolean pIsLeafNode)
        {
            TLzCustomExpression lcexpr = (TLzCustomExpression)pnode;
            if (lcexpr.oper == TLzOpType.Expr_AND)
            {
                lcexpr.opname.AsText = "&&";
            }
            else if (lcexpr.oper == TLzOpType.Expr_Comparison)
            {
                if (lcexpr.opname.AsText == "=")
                    lcexpr.opname.AsText = "==";
                if (lcexpr.opname.AsText == "<>")
                    lcexpr.opname.AsText = "!=";
            }
            else if (lcexpr.oper == TLzOpType.Expr_OR)
            {
                lcexpr.opname.AsText = "||";
            }
            else if (lcexpr.oper == TLzOpType.Expr_Attr)
            {
                lcexpr.AsText = "this." + lcexpr.AsText;
            }

            return true;
        }
    }
    public static class util
    {
        public static string HandleLike(this string s)
        {
            while (s.ToLower().Contains("like"))
            {
                List<string> arr = s.Split(' ').ToList();
                arr.RemoveAll(d => d == "");
                string a = "", b = "";
                for (int i = 0; i < arr.Count; i++)
                {
                    if (arr[i].ToLower() == "like")
                    {
                        a = arr[i - 1];
                        b = arr[i + 1];
                        arr.RemoveAt(i);
                        arr.Remove(a);
                        arr.Remove(b);
                        break;
                    }
                }
                b = b.Replace("'", "").Replace("%", @".*");
                string ss = string.Concat(arr.ToArray()).Trim('&');
                ss = string.Concat(ss, string.Format("&& new RegExp('^{0}$', 'gi').test({1})", b, a));
                s = ss;
            }
            return s.TrimStart('&');
        }
    }
}
